package com.ycxh.module.dbtoword.util;

import com.ycxh.module.dbtoword.domain.TableFileds;
import com.ycxh.module.dbtoword.domain.Tables;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * jdbc连接mysql
 */
@Component
@Data
public class JdbcMysql {

    // mysql数据库驱动
    // 5.0的写法可以用在8.0中，但是8.0的写法不能用在5.0当中
    private static String driver = "com.mysql.cj.jdbc.Driver";
    // MySQL地址
    // private static String url = "jdbc:mysql://127.0.0.1:3306/mysql?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    // nullCatalogMeansCurrent
    // 避坑！Mybatis Generator 生成文件的时候错误使用了其它数据库的同名数据表（关于nullCatalogMeansCurrent参数） https://www.cnblogs.com/coderLeo/p/12770673.html
    // 学习笔记：一个MySQL实例有多个Activiti数据库问题 https://cloud.tencent.com/developer/article/1667423
    // databaseTerm=SCHEMA Druid setSchema无效 https://blog.csdn.net/u011470905/article/details/118516743
    private static String url = "jdbc:mysql://127.0.0.1:3306/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&nullCatalogMeansCurrent=true&databaseTerm=SCHEMA";
    // 数据库用户名
    private static String userName = "root";
    // 数据库密码
    private static String password = "123456";

    static Logger logger = LoggerFactory.getLogger(JdbcMysql.class);

    /*
     * 使用外部传入的链接字符串
     * */
    public JdbcMysql(String datasourceUrl) {
        url = datasourceUrl;
    }

    /**
     * 使用外部传入的参数
     *
     * @param datasourceUrl
     * @param username
     * @param passwd
     */
    public JdbcMysql(String datasourceUrl, String username, String passwd) {
        url = datasourceUrl;
        userName = username;
        password = passwd;
    }

    /**
     * 默认@Autowired的构造方法
     */
    public JdbcMysql() {
    }

    // 获取数据库连接
    public static Connection getConn() {
        Connection conn = null;
        try {
            // 加载驱动
            Class.forName(driver);
            DriverManager.setLoginTimeout(30);
            // 获取数据库连接 超时时间需要在url里面设置 不然连接不上会卡死
            conn = (Connection) DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            // com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }


    // 关闭数据库资源
    public static void closeAll(ResultSet rs, Statement stat, Connection conn) {
        // 分别按顺序关闭数据库的结果集资源，Statement 对象资源以及Connection 连接数据库对象
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取指定数据库下所有表名和注释
     *
     * @param dbName 数据库名
     */
    public List<Tables> getAllTables(String dbName) {
        List<Tables> list = null;
        Connection conn = JdbcMysql.getConn();
        if (conn == null) {
            return null;
        }
        try {
            list = getAllTables(dbName, conn);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            closeAll(null, null, conn);
        }
        return list;
    }

    public List<Tables> getAllTables(String dbName, Connection conn) {
        String sql = "select table_name as name,table_comment as comment from information_schema.tables where table_schema = ? order by table_name";

        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Tables> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            // 设置sql语句的参数
            ps.setString(1, dbName);

            // 设置超时时间
            ps.setQueryTimeout(1000);
            rs = ps.executeQuery();
            while (rs.next()) {
                Tables table = new Tables();
                table.setName(rs.getString("name"));
                table.setComment(rs.getString("comment"));
                list.add(table);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e.getStackTrace().toString());
        } finally {
            // 关闭连接
            closeAll(rs, ps, null);
        }
        return list;
    }

    /**
     * 获取指定表信息
     *
     * @param tableName 表名名
     */
    public List<TableFileds> getTable(String dbName, String tableName) throws Exception {
        Connection conn = JdbcMysql.getConn();
        List<TableFileds> fileds = null;
        try {
            fileds = getTable(dbName, tableName);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭连接
            closeAll(null, null, conn);
        }

        return fileds;
    }

    public List<TableFileds> getTable(String dbName, String tableName, Connection conn) throws SQLException {
        if (conn == null) {
            return null;
        }

        // Connection获取数据库、表、字段元数据 https://blog.csdn.net/m4330187/article/details/118458981
        // ResultSet tables = conn.getMetaData().getTables(null, "test", null, null);
        // 只查询当前库，数据库连接加参数&nullDatabaseMeansCurrent=true 不加会查询所有库
        ResultSet tables = conn.getMetaData().getTables(null, null, null, null);
        while (tables.next()) {
            String TABLE_CAT = tables.getString("TABLE_CAT");
            String TABLE_SCHEM = tables.getString("TABLE_SCHEM");
            String TABLE_NAME = tables.getString("TABLE_NAME");
            String TABLE_TYPE = tables.getString("TABLE_TYPE");
            // 表描述为null 数据库连接加参数&useInformationSchema=true
            String REMARKS = tables.getString("REMARKS");

            System.out.println(MessageFormat.format("表类别：{0}、表模式：{1}、表名称：{2}、表类型：{3}、表描述：{4}", TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS));
        }


        // 动态绑定，是不支持表名绑定的，使用预编译会发生语法错误，只能用拼接的做法
        // String sql = "SHOW FULL FIELDS FROM ? ";
        // 会添加库名前缀 不管有没有手动添加前缀
        // String sql = "SHOW FULL FIELDS FROM `" + dbName + "." + tableName + "`";
        String sql = "SHOW FULL FIELDS FROM `" + tableName + "`";
        // logger.info("Jdbc.getTable：执行的SQL:" + sql);

        // 设置数据库无效 databaseTerm=SCHEMA 并升级 mysql-connector-java 版本，低版本设置无效, 比如从 8.0.16 升到 8.0.28
        // 原因：Druid setSchema无效 https://blog.csdn.net/u011470905/article/details/118516743
        // ConnectionImpl.setSchema()方法里，this.propertySet.getEnumProperty(PropertyKey.databaseTerm).getValue() 的值是 DatabaseTerm.CATALOG
        // 解决办法：记录下mybatis generator生成代码的坑 https://www.jianshu.com/p/24eb67818155
        // 如果切库不生效：方式1：直接修改数据库连接url里的默认库，方式2：数据库连接url添加参数，&databaseTerm=schema
        // https://www.null123.com/question/detail-2262202.html https://dev.mysql.com/doc/connectors/en/connector-j-connp-props-connection.html#cj-conn-prop_databaseTerm
        // https://mysql.net.cn/doc/connector-j/8.0/en/connector-j-connp-props-connection.html#cj-conn-prop_databaseTerm
        // 如果"databaseTerm=CATALOG"，则忽略用于搜索的"schemaPattern"，并且对架构方法（如"setSchema()"或"getSchema()"）的调用将变为no-ops，反之亦然。 默认值为databaseTerm=CATALOG。
        conn.setSchema(dbName); // catalog目录，schema模式  DatabaseMetaData nullDatabaseMeansCurrent
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<TableFileds> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            // 设置sql语句的参数
            // ps.setString(1, tableName);

            // 设置超时时间
            ps.setQueryTimeout(1000);
            // java.sql.SQLSyntaxErrorException: Table 'mysql.table_name' doesn't exist
            rs = ps.executeQuery();
            while (rs.next()) {
                TableFileds table = new TableFileds();
                table.setField(rs.getString("field"));
                table.setType(rs.getString("Type"));
                table.setType(rs.getString("type"));
                table.setNull(rs.getString("Null"));
                table.setKey(rs.getString("Key"));
                table.setComment(rs.getString("Comment"));
                table.setDefault(rs.getString("Default"));
                // auto_increment
                System.out.println(rs.getString("Extra"));
                list.add(table);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e.getStackTrace().toString());
        } finally {
            // 关闭连接
            closeAll(rs, ps, null);
        }
        return list;
    }

}
